package com.example.qxfw.ybzz.mapper;

import com.baomidou.dynamic.datasource.annotation.DS;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.qxfw.common.entity.qx.YbzzForecastFinal;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.sql.Timestamp;
import java.util.Date;
import java.util.List;

@Mapper
@DS("service")
public interface YbzzForecastFinalMapper extends BaseMapper<YbzzForecastFinal> {
    @Delete("delete from ybzz_Forecastfinal where " +
            "cast(datechar as timestamp)+ CAST(ntimes||' hour' as INTERVAL) > ${shuZhiDate} AND " +
            "cast(datechar as timestamp)+ CAST(ntimes||' hour' as INTERVAL) <= ${shuZhiEndDate} and" +
            " ForecastType=${intForecastType} and stationid in (${stationList})")
    Integer delete(String shuZhiDate, String shuZhiEndDate, String intForecastType, String stationList);

    @Select("select datechar + cast(ntimes ||'hour' as INTERVAL) datechar, temperature, humid, wind " +
            "from ybzz_Forecastfinal " +
            "where forecasttype = 1 and (datechar + cast(ntimes || 'hour' as INTERVAL)) >= '${dateChar1}' and (datechar + cast(ntimes || 'hour' as INTERVAL)) < '${dateChar2}' and stationid = '${stationId}'")
    List<YbzzForecastFinal> selectByParams(Timestamp dateChar1, Timestamp dateChar2, String stationId);

    @Select("select max(temperature) maxtemp, min(temperature) mintemp, avg(temperature) temperature ,avg(humid) humid ,avg(wind) wind  " +
            "from ybzz_Forecastfinal " +
            "where forecasttype = 1 and (datechar + cast(ntimes || 'hour' as INTERVAL)) >= '${dateChar1}' and (datechar + cast(ntimes || 'hour' as INTERVAL)) < '${dateChar2}' and stationid = '${stationId}'")
    List<YbzzForecastFinal> selectByParams1(Timestamp dateChar1, Timestamp dateChar2, String stationId);

    /**
     * @param dateChar
     * @param forecastType
     * @param stationId
     * @return
     */
    @Select("SELECT StationID, " +
            "       AVG(Temperature) as Temperature, " +
            "       AVG(Humid) as Humid, " +
            "       MIN(Humid) as minhumid, " +
            "       SUM(Rain) as Rain, " +
            "       MAX(Wind) as Wind, " +
            "       AVG(Wind) as Windd " +
            "FROM ybzz_forecastfinal " +
            "WHERE DateChar = #{dateChar} " +
            "      AND forecastType = #{forecastType} " +
            "      AND StationID LIKE #{stationId} " +
            "GROUP BY StationID  ")
    List<YbzzForecastFinal> selectByDateAndTypeAndIdOfYbzzForecastFinal(
            Date dateChar, Integer forecastType, String stationId);

    @Select("SELECT StationID, Temperature, Wind, Humid " +
            "FROM ybzz_forecastfinal " +
            "WHERE DateChar = #{dateChar} " +
            "AND forecastType = #{forecastType} " +
            "AND StationID = #{stationId} " +
            "AND NTimes = #{ntimes} ")
    List<YbzzForecastFinal> selectByDateAndTypeAndIdAndNTimesOfYbzzForecastFinal(
            Date dateChar, Integer forecastType, String stationId, Integer ntimes);


}
